﻿#include "mapdb.h"
#include "CppSQLite3.h"
//#include "common.h"
#include "base64.h"
#include <sstream>
#include <algorithm>
#include <iomanip>

MapDB::MapDB(OneMap *g_onemapIn, CppSQLite3DB *g_MapdbIn)
{
    g_onemap=g_onemapIn;
    g_Mapdb=g_MapdbIn;
}

int MapDB::checkTable()
{

    int tmpKey=1;
    //检查表
    try {
        if (!g_Mapdb->tableExists("agv_station")) {
            tmpKey=0;
            g_Mapdb->execDML("create table agv_station(id INTEGER,name char(64),type INTEGER, \
                            x DOUBLE,y DOUBLE,realX DOUBLE,realY DOUBLE,realA DOUBLE DEFAULT 0, \
                       labelXoffset INTEGER,labelYoffset INTEGER,mapChange BOOL,locked BOOL,ip char(64),\
                     port INTEGER,agvType INTEGER,lineId char(64),actionCommand char(64), poseType INTEGER);");
        }
        if (!g_Mapdb->tableExists("agv_line")) {
            tmpKey=0;
            g_Mapdb->execDML("create table agv_line(id INTEGER,name char(64),type INTEGER,\
                    start INTEGER,end INTEGER,p1x DOUBLE,p1y DOUBLE,p2x DOUBLE,p2y DOUBLE,\
                length DOUBLE,locked BOOL,speed DOUBLE DEFAULT 0.4, barrierType INTEGER, isBackMove INTEGER,\
                p1x_real DOUBLE,p1y_real DOUBLE,p2x_real DOUBLE,p2y_real DOUBLE);");
        }
        if (!g_Mapdb->tableExists("agv_bkg")) {
            tmpKey=0;
            g_Mapdb->execDML("create table agv_bkg(id INTEGER,name char(64),data blob,\
            data_len INTEGER,x DOUBLE,y DOUBLE,width INTEGER,height INTEGER,filename char(512));");
        }
        if (!g_Mapdb->tableExists("agv_floor")) {
            tmpKey=0;
            g_Mapdb->execDML("create table agv_floor(id INTEGER,name char(64),point INTEGER,\
                path INTEGER,bkg INTEGER,originX INTEGER,originY INTEGER,rate DOUBLE);");
        }
        if (!g_Mapdb->tableExists("agv_block")) {
            tmpKey=0;
            g_Mapdb->execDML("create table agv_block(id INTEGER,name char(64),spirits char(512));");
        }
        if (!g_Mapdb->tableExists("agv_group")) {
            tmpKey=0;
            g_Mapdb->execDML("create table agv_group(id INTEGER,name char(64),spirits char(512),groupType INTEGER);");
        }
        if (!g_Mapdb->tableExists("map_common")) {
            tmpKey=0;
            g_Mapdb->execDML("create table map_common(id INTEGER,maxId INTEGER,version INTEGER,\
                                displayRatio DOUBLE,modifyDate char(64));");
        }
    }
    catch (CppSQLite3Exception &e) {
#ifdef FOR_FLEET_SERVER
        combined_logger->error("sqlerr code:{0} msg:{1}", e.errorCode(), e.errorMessage());
#else
        qDebug()<<"CppSQLite3Exception :"<<e.errorMessage();
#endif
        return -1;
    }
    catch (std::exception e) {
#ifdef FOR_FLEET_SERVER
        combined_logger->error("sqlerr code:{0}", e.what());
#else
        QByteArray tmpInfo=QByteArray(e.what());
        qDebug()<<"sqlerr:"<<tmpInfo;
#endif

        return -2;
    }
    return tmpKey;
}

//载入地图
bool MapDB::load()
{
//    try
//    {
//        g_Mapdb->close();
//        g_Mapdb->open(MAP_DB_File);//替换db文件后，该句需要重新执行才能生效。
//    }
//    catch (CppSQLite3Exception &e)
//    {
//       qDebug()<<"open map db,sqlite error";// {0}:{1};", e.errorCode(), e.errorMessage());
//       return false;
//    }

    if(1!=checkTable())
    {
        return false;
    }
    //载入数据
    mapModifying = true;
    if (!loadFromDb())
    {
        mapModifying = false;
        return false;
    }
    mapModifying = false;
    return true;
}


//保存到数据库
bool MapDB::save()
{
    try {
        if(1!=checkTable())
        {
            return false;
        }

        g_Mapdb->execDML("delete from agv_station;");

        g_Mapdb->execDML("delete from agv_line;");

        g_Mapdb->execDML("delete from agv_bkg;");

        g_Mapdb->execDML("delete from agv_floor;");

        g_Mapdb->execDML("delete from agv_block;");

        g_Mapdb->execDML("delete from agv_group;");

        g_Mapdb->execDML("delete from map_common;");

        g_Mapdb->execDML("begin transaction;");

        std::list<MapSpirit *> spirits = g_onemap->getAllElement();

        CppSQLite3Buffer bufSQL;

        for (auto spirit : spirits)
        {
            if (spirit->getSpiritType() == MapSpirit::Map_Sprite_Type_Point) {
                MapPoint *station = static_cast<MapPoint *>(spirit);
                bufSQL.format("insert into agv_station(id,name,type, x,y,realX,realY,realA,\
                              labelXoffset,labelYoffset ,mapChange,locked,ip,port,agvType,lineId\
                              ,actionCommand,poseType                                                                 ) \
                              values (%d, '%s',%d,%.3f,%.3f,%.3f,%.3f,%.3f,%d,%d,%d,%d,'%s',%d,%d,'%s','%s',%d);"
                              , station->getId(), station->getName().c_str(), station->getPointType(),
                              station->getX(), station->getY(),
                              station->getRealX(), station->getRealY(), station->getRealA(),
                              station->getLabelXoffset(), station->getLabelYoffset(),
                              station->getMapChange(), station->getLocked(), station->getIp().c_str(),
                              station->getPort(), station->getAgvType(),station->getLineId().c_str()
                        ,station->getActionCommand().c_str(),station->getPoseType());
                g_Mapdb->execDML(bufSQL);
            }
            else if (spirit->getSpiritType() == MapSpirit::Map_Sprite_Type_Path) {
                MapPath *path = static_cast<MapPath *>(spirit);
                bufSQL.format("insert into agv_line(id ,name,type,barrierType,isBackMove ,start ,end ,p1x ,p1y ,p2x ,p2y \
                                 ,speed,locked,length,p1x_real ,p1y_real ,p2x_real ,p2y_real) \
                                values (%d,'%s', %d, %d, %d,%d, %d, %.3f, %.3f, %.3f, %.3f, %.3f, %d,%.3f,%.3f,%.3f,%.3f,%.3f);"\
                , path->getId(), path->getName().c_str(), path->getPathType(),path->getBarrierType(),path->getIsBackMove()
                        , path->getStart(), path->getEnd(),
                path->getP1x(), path->getP1y(), path->getP2x(), path->getP2y(),
                path->getSpeed(),path->getLocked(),path->getLength(),path->getP1x_real(), path->getP1y_real(), path->getP2x_real(), path->getP2y_real());
                g_Mapdb->execDML(bufSQL);//类型必须一致，函数才能正常拼接字符串。
//                QString tmpCommand;
//                tmpCommand.sprintf("insert into agv_line(id ,name,type,barrierType,isBackMove ,start ,end ,p1x ,p1y ,p2x ,p2y \
//                                   ,speed,locked,length) \
//                                  values (%d,'%s', %d, %d, %d,%d, %d, %.3f, %.3f, %.3f, %.3f, %.3f, %d,%.3f);"\
//                  , path->getId(), path->getName().c_str(), path->getPathType(),path->getBarrierType(),path->getIsBackMove()
//                          , path->getStart(), path->getEnd(),
//                  path->getP1x(), path->getP1y(), path->getP2x(), path->getP2y(),
//                  path->getSpeed(),path->getLocked(),path->getLength());
//                qDebug()<<"value"<<path->getSpeed()<<path->getLocked()<<path->getLength();
//                g_Mapdb->execDML(tmpCommand.toLatin1().data());
            }
            else if (spirit->getSpiritType() == MapSpirit::Map_Sprite_Type_Background) {
                MapBackground *bkg = static_cast<MapBackground *>(spirit);
                CppSQLite3Binary blob;
                blob.setBinary((unsigned char *)bkg->getImgData(), bkg->getImgDataLen());
                bufSQL.format("insert into agv_bkg(id ,name,data,data_len,x ,y ,width ,height ,filename) \
                            values (%d,'%s',%Q, %d,%.3f, %.3f, %d, %d,'%s');",
                        bkg->getId(), bkg->getName().c_str(), blob.getEncoded(), bkg->getImgDataLen(),
                        bkg->getX(), bkg->getY(), bkg->getWidth(), bkg->getHeight(), bkg->getFileName().c_str());
                g_Mapdb->execDML(bufSQL);
            }
            else if (spirit->getSpiritType() == MapSpirit::Map_Sprite_Type_Floor) {
                MapFloor *floor = static_cast<MapFloor *>(spirit);
                std::stringstream pointstr;
                std::list<int> ps = floor->getPoints();
                for (auto p : ps) {
                    pointstr << p << ";";
                }
                std::stringstream pathstr;
                std::list<int> pas = floor->getPaths();
                for (auto pa : pas) {
                    pathstr << pa << ";";
                }
                bufSQL.format("insert into agv_floor(id ,name,point,path,bkg,originX,originY,rate) \
                                values (%d,'%s', '%s','%s',%d,%d,%d,%lf);",
                        floor->getId(), floor->getName().c_str(), pointstr.str().c_str(),
                        pathstr.str().c_str(), floor->getBkg(), floor->getOriginX(),
                        floor->getOriginY(), floor->getRate());
                g_Mapdb->execDML(bufSQL);
            }
            else if (spirit->getSpiritType() == MapSpirit::Map_Sprite_Type_Block) {
                MapBlock *block = static_cast<MapBlock *>(spirit);
                std::stringstream str;
                std::list<int> ps = block->getSpirits();
                for (auto p : ps)str << p << ";";
                bufSQL.format("insert into agv_block(id ,name,spirits) values (%d,'%s', '%s');", block->getId(), block->getName().c_str(), str.str().c_str());
                g_Mapdb->execDML(bufSQL);
            }
            else if (spirit->getSpiritType() == MapSpirit::Map_Sprite_Type_Group) {
                MapGroup *group = static_cast<MapGroup *>(spirit);

                std::stringstream str1;
                std::list<int> ps1 = group->getSpirits();
                for (auto p : ps1)str1 << p << ";";

                bufSQL.format("insert into agv_group(id ,name,spirits,groupType) \
                    values (%d,'%s', '%s',%d);", group->getId(), group->getName().c_str(), str1.str().c_str(), group->getGroupType());
                g_Mapdb->execDML(bufSQL);
            }


        }
        bufSQL.format("insert into map_common(id,maxId,version, displayRatio,modifyDate                                                                 ) \
                      values (%d,%d,%d,%.3f,'%s');",1,g_onemap->getMaxId(),g_onemap->getVersion(),
                            g_onemap->getDisplayRatio()," ");
        g_Mapdb->execDML(bufSQL);

        g_Mapdb->execDML("commit transaction;");
    }
    catch (CppSQLite3Exception &e) {
#ifdef FOR_FLEET_SERVER
         combined_logger->error("sqlerr code:{0} msg:{1}", e.errorCode(), e.errorMessage());
#else
        qDebug()<<"sqlerr:"<<e.errorMessage();
#endif

        return false;
    }
    catch (std::exception e) {
#ifdef FOR_FLEET_SERVER
         combined_logger->error("sqlerr code:{0}", e.what());
#else
        QByteArray tmpInfo=QByteArray(e.what());
        qDebug()<<"sqlerr:"<<tmpInfo;
#endif

        return false;
    }
    return true;
}

//从数据库中载入地图
bool MapDB::loadFromDb()
{
    g_onemap->clear();
    try {
        if(1!=checkTable())
        {
            return false;
        }

        CppSQLite3Table table_station = g_Mapdb->getTable("select id, name, x ,y ,type ,realX ,realY ,\
                    realA, labelXoffset ,labelYoffset ,mapChange ,locked ,ip ,port ,\
                    agvType ,lineId , actionCommand , poseType from agv_station;");
//        if (table_station.numRows() > 0 && table_station.numFields() != 16)
//        {
//#ifdef FOR_FLEET_SERVER
//         combined_logger->error("MapDB loadFromDb agv_station error!");
//#else
//        qDebug()<<"sqlerr table_station.numRows() > 0 && table_station.numFields() != 16";
//#endif

//            return false;
//        }
        for (int row = 0; row < table_station.numRows(); row++)
        {
            table_station.setRow(row);

            int id = atoi(table_station.fieldValue(0));
            std::string name = std::string(table_station.fieldValue(1));
            double x = atof(table_station.fieldValue(2));
            double y = atof(table_station.fieldValue(3));
            int type = atoi(table_station.fieldValue(4));
            double realX = atof(table_station.fieldValue(5));
            double realY = atof(table_station.fieldValue(6));
            double realA = atof(table_station.fieldValue(7));
            int labeXoffset = atoi(table_station.fieldValue(8));
            int labelYoffset = atoi(table_station.fieldValue(9));
            bool mapchange = atoi(table_station.fieldValue(10)) == 1;
            bool locked = atoi(table_station.fieldValue(11)) == 1;
            std::string ip = std::string(table_station.fieldValue(12));
            int port = atoi(table_station.fieldValue(13));
            int agvType = atoi(table_station.fieldValue(14));
            std::string lineId = std::string(table_station.fieldValue(15));

            std::string actionCommand = std::string(table_station.fieldValue(16));
            int poseType = atoi(table_station.fieldValue(17));

            MapPoint::Map_Point_Type t = static_cast<MapPoint::Map_Point_Type>(type);
            MapPoint *point = new MapPoint(id, name, t, x, y, realX, realY, realA, labeXoffset, labelYoffset, mapchange, locked, ip, port, agvType, lineId);
            point->setActionCommand(actionCommand);
            point->setPoseType((E_POSITION_TYPE)poseType);
            g_onemap->addSpirit(point);
        }

        CppSQLite3Table table_line = g_Mapdb->getTable("select id,name,type,start,end,\
                            p1x,p1y,p2x,p2y,length,locked,speed,barrierType,isBackMove,p1x_real,p1y_real,p2x_real,p2y_real  \
                                     from agv_line;");
//        if (table_line.numRows() > 0 && table_line.numFields() != 12)return false;
        for (int row = 0; row < table_line.numRows(); row++)
        {
            table_line.setRow(row);

            int id = atoi(table_line.fieldValue(0));
            std::string name = std::string(table_line.fieldValue(1));
            int type = atoi(table_line.fieldValue(2));
            int start = atoi(table_line.fieldValue(3));
            int end = atoi(table_line.fieldValue(4));
            double p1x = atof(table_line.fieldValue(5));
            double p1y = atof(table_line.fieldValue(6));
            double p2x = atof(table_line.fieldValue(7));
            double p2y = atof(table_line.fieldValue(8));
            int length = atoi(table_line.fieldValue(9));
            bool locked = atoi(table_line.fieldValue(10)) == 1;
            double speed = atof(table_line.fieldValue(11));
            int barrierType = atoi(table_line.fieldValue(12));
            int isBackMove = atoi(table_line.fieldValue(13));
            double p1x_real = atof(table_line.fieldValue(14));
            double p1y_real = atof(table_line.fieldValue(15));
            double p2x_real = atof(table_line.fieldValue(16));
            double p2y_real = atof(table_line.fieldValue(17));


            MapPath::Map_Path_Type t = static_cast<MapPath::Map_Path_Type>(type);
            MapPath *path = new MapPath(id, name, start, end, t, length, p1x, p1y, p2x, p2y, locked,speed,
                                        p1x_real,p1y_real, p2x_real, p2y_real);
            path->setBarrierType((E_PATH_BARRIER_TYPE)barrierType);
            path->setIsBackMove(isBackMove);

            g_onemap->addSpirit(path);
        }

        CppSQLite3Table table_bkg = g_Mapdb->getTable("select id,name,data,data_len,x,y,width,height,filename from agv_bkg;");
//        if (table_bkg.numRows() > 0 && table_bkg.numFields() != 9)return false;
        for (int row = 0; row < table_bkg.numRows(); row++)
        {
            table_bkg.setRow(row);

            int id = atoi(table_bkg.fieldValue(0));
            std::string name = std::string(table_bkg.fieldValue(1));

            CppSQLite3Binary blob;
            blob.setEncoded((unsigned char*)table_bkg.fieldValue(2));
            unsigned char *data = new unsigned char[blob.getBinaryLength()];
            memcpy(data, blob.getBinary(), blob.getBinaryLength());
            int data_len = atoi(table_bkg.fieldValue(3));
            double x = atof(table_bkg.fieldValue(4));
            double y = atof(table_bkg.fieldValue(5));
            int width = atoi(table_bkg.fieldValue(6));
            int height = atoi(table_bkg.fieldValue(7));
            std::string filename = std::string(table_bkg.fieldValue(8));
            MapBackground *bkg = new MapBackground(id, name, (char *)data, data_len, width, height, filename);
            bkg->setX(x);
            bkg->setY(y);
            g_onemap->addSpirit(bkg);
        }


        CppSQLite3Table table_floor = g_Mapdb->getTable("select id,name,point,path,bkg,originX,originY,rate from agv_floor;");
//        if (table_floor.numRows() > 0 && table_floor.numFields() != 8)return false;
        for (int row = 0; row < table_floor.numRows(); row++)
        {
            table_floor.setRow(row);

            int id = atoi(table_floor.fieldValue(0));
            std::string name = std::string(table_floor.fieldValue(1));

            MapFloor *mfloor = new MapFloor(id, name);

            std::string pointstr = std::string(table_floor.fieldValue(2));
            std::string pathstr = std::string(table_floor.fieldValue(3));
            int bkg = atoi(table_floor.fieldValue(4));
            int originX = atoi(table_floor.fieldValue(5));
            int originY = atoi(table_floor.fieldValue(6));
            double rate = atof(table_floor.fieldValue(7));

            std::vector<std::string> pvs = split_str(pointstr, ";");
            for (auto p : pvs) {
                int intp;
                std::stringstream ss;
                ss << p;
                ss >> intp;
                mfloor->addPoint(intp);
            }

            std::vector<std::string> avs = split_str(pathstr, ";");
            for (auto p : avs) {
                int intp;
                std::stringstream ss;
                ss << p;
                ss >> intp;
                mfloor->addPath(intp);
            }

            mfloor->setBkg(bkg);
            mfloor->setOriginX(originX);
            mfloor->setOriginY(originY);
            mfloor->setRate(rate);
            g_onemap->addSpirit(mfloor);
        }


        CppSQLite3Table table_block = g_Mapdb->getTable("select id,name,spirits from agv_block;");
//        if (table_block.numRows() > 0 && table_block.numFields() != 3)return false;
        for (int row = 0; row < table_block.numRows(); row++)
        {
            table_block.setRow(row);

            int id = atoi(table_block.fieldValue(0));
            std::string name = std::string(table_block.fieldValue(1));

            MapBlock *mblock = new MapBlock(id, name);

            std::string pointstr = std::string(table_block.fieldValue(2));
            std::vector<std::string> pvs = split_str(pointstr, ";");
            for (auto p : pvs) {
                int intp;
                std::stringstream ss;
                ss << p;
                ss >> intp;
                mblock->addSpirit(intp);
            }

            g_onemap->addSpirit(mblock);
        }

        CppSQLite3Table table_group = g_Mapdb->getTable("select id,name,spirits,groupType from agv_group;");
//        if (table_group.numRows() > 0 && table_group.numFields() != 4)return false;
        for (int row = 0; row < table_group.numRows(); row++)
        {
            table_group.setRow(row);

            int id = atoi(table_group.fieldValue(0));
            std::string name = std::string(table_group.fieldValue(1));
            int groupType = atoi(table_group.fieldValue(3));

            MapGroup *mgroup = new MapGroup(id, name, groupType);

            std::string pointstr = std::string(table_group.fieldValue(2));

            std::vector<std::string> pvs = split_str(pointstr, ";");
            for (auto p : pvs) {
                int intp;
                std::stringstream ss;
                ss << p;
                ss >> intp;
                mgroup->addSpirit(intp);
            }

            g_onemap->addSpirit(mgroup);
        }

        CppSQLite3Table table_map_common = g_Mapdb->getTable("select id,maxId,version,\
                            displayRatio,modifyDate \
                             from map_common;");
//        if (table_map_common.numRows() > 0 && table_map_common.numFields() != 4)return false;
        for (int row = 0; row < table_map_common.numRows(); row++)
        {
            table_map_common.setRow(row);

            int max_id = atoi(table_map_common.fieldValue(1));
            g_onemap->setMaxId(max_id);
            int version= atoi(table_map_common.fieldValue(2));
            g_onemap->setVersion(version);
            double displayRatio=atof(table_map_common.fieldValue(3));
            g_onemap->setDisplayRatio(displayRatio);

        }

//        int max_id = 0;
//        auto ps = g_onemap->getAllElement();
//        for (auto p : ps) {
//            if (p->getId() > max_id)max_id = p->getId();
//        }
//        g_onemap->setMaxId(max_id);
//        getReverseLines();
//        getAdj();
//        check();
    }
    catch (CppSQLite3Exception &e) {
#ifdef FOR_FLEET_SERVER
        combined_logger->error("sqlerr code:{0} msg:{1}", e.errorCode(), e.errorMessage());
#else
        qDebug()<<"sqlerr:"<<e.errorMessage();
#endif

        return false;
    }
    catch (std::exception e) {
#ifdef FOR_FLEET_SERVER
         combined_logger->error("sqlerr code:{0}", e.what());
#else
        QByteArray tmpInfo=QByteArray(e.what());
        qDebug()<<"sqlerr:"<<tmpInfo;
#endif

        return false;
    }
    return true;
}



void MapDB::clear()
{
    g_onemap->clear();
}



//check if exists all element
//void MapDB::check()
//{
//    //check lines!
//    bool changed = false;
//    auto paths = g_onemap->getPaths();
//    for(auto path:paths){
//        auto start = path->getStart();
//        auto end = path->getEnd();
//        if(g_onemap->getPointById(start) == nullptr
//                ||g_onemap->getPointById(end) == nullptr){
//            g_onemap->removeSpiritById(path->getId());
//            changed = true;
//        }
//    }
//    if(changed){
//        save();
//        changed = false;
//    }

//    //check floor!
//    auto floors = g_onemap->getFloors();
//    for(auto floor:floors){
//        auto points = floor->getPoints();
//        for(auto p:points){
//            if(g_onemap->getPointById(p) == nullptr){
//                floor->removePoint(p);
//                changed = true;
//            }
//        }
//        auto pathss = floor->getPaths();
//        for(auto pp:pathss){
//            if(g_onemap->getPathById(pp) == nullptr){
//                floor->removePath(pp);
//                changed = true;
//            }
//        }
//    }
//    if(changed){
//        save();
//        changed = false;
//    }

//    //check group!
//    auto groups = g_onemap->getGroups();
//    for(auto group:groups){
//        auto ps = group->getSpirits();
//        for(auto p:ps){
//            if(g_onemap->getPointById(p)==nullptr
//                    &&g_onemap->getPathById(p) == nullptr){
//                group->removeSpirit(p);
//                changed = true;
//            }
//        }
//    }
//    if(changed){
//        save();
//        changed = false;
//    }

//    //check block!
//    auto blocks = g_onemap->getBlocks();
//    for(auto block:blocks){
//        auto ps = block->getSpirits();
//        for(auto p:ps){
//            if(g_onemap->getPointById(p)==nullptr
//                    &&g_onemap->getPathById(p) == nullptr){
//                block->removeSpirit(p);
//                changed = true;
//            }
//        }
//    }
//    if(changed){
//        save();
//        changed = false;
//    }
//}


std::vector<std::string> MapDB::split_str(std::string src,std::string sp)
{
    std::vector<std::string> result;
    if (src.length() == 0)return result;
    if(sp.length()==0){
        result.push_back(src);
        return result;
    }

    size_t pos;
    while(true){
        pos = src.find_first_of(sp);
        if(pos == std::string::npos){
            break;
        }
        if(pos!=0)
            result.push_back(src.substr(0,pos));
        src = src.substr(pos+sp.length());
    }
    if(src.length()>0)
        result.push_back(src);
    return result;
}
